--    Program: 		CAPARIMP_RECEIPT_HISTORY.sql
--    Description:  Import AR RECEIPT HISTORY information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_cash_receipt_history_id	number;
                    		                 		
   cursor cur_ar_receipt_history is
      select * from CRST_AR_CASH_RECEIPT_HISTORY
      --WHERE CASH_RECEIPT_HISTORY_ID = 14084
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_receipt_history loop   	
     l_err_msg_tmp := '';
     
     SELECT AR_CASH_RECEIPT_HISTORY_S.NEXTVAL 
     INTO l_cash_receipt_history_id
     FROM DUAL;
     
     update crst_ar_cash_receipt_history
     set    cash_receipt_history_id_n = l_cash_receipt_history_id
     where  cash_receipt_history_id = cur.CASH_RECEIPT_HISTORY_ID;
     
     begin
       -- import data
       insert into AR_CASH_RECEIPT_HISTORY_ALL
       (
          CASH_RECEIPT_HISTORY_ID        ,
          CASH_RECEIPT_ID                ,
          STATUS                         ,
          TRX_DATE                       ,
          AMOUNT                         ,
          FIRST_POSTED_RECORD_FLAG       ,
          POSTABLE_FLAG                  ,
          FACTOR_FLAG                    ,
          GL_DATE                        ,
          CURRENT_RECORD_FLAG            ,
          BATCH_ID                       ,
          ACCOUNT_CODE_COMBINATION_ID    ,
          REVERSAL_GL_DATE               ,
          REVERSAL_CASH_RECEIPT_HIST_ID  ,
          FACTOR_DISCOUNT_AMOUNT         ,
          BANK_CHARGE_ACCOUNT_CCID       ,
          POSTING_CONTROL_ID             ,
          REVERSAL_POSTING_CONTROL_ID    ,
          GL_POSTED_DATE                 ,
          REVERSAL_GL_POSTED_DATE        ,
          LAST_UPDATE_LOGIN              ,
          ACCTD_AMOUNT                   ,
          ACCTD_FACTOR_DISCOUNT_AMOUNT   ,
          CREATED_BY                     ,
          CREATION_DATE                  ,
          EXCHANGE_DATE                  ,
          EXCHANGE_RATE                  ,
          EXCHANGE_RATE_TYPE             ,
          LAST_UPDATE_DATE               ,
          PROGRAM_APPLICATION_ID         ,
          PROGRAM_ID                     ,
          PROGRAM_UPDATE_DATE            ,
          REQUEST_ID                     ,
          LAST_UPDATED_BY                ,
          PRV_STAT_CASH_RECEIPT_HIST_ID  ,
          CREATED_FROM                   ,
          REVERSAL_CREATED_FROM          , 
          ATTRIBUTE1                     ,
          ATTRIBUTE2                     ,
          ATTRIBUTE3                     ,
          ATTRIBUTE4                     ,
          ATTRIBUTE5                     ,
          ATTRIBUTE6                     ,
          ATTRIBUTE7                     ,
          ATTRIBUTE8                     ,
          ATTRIBUTE9                     ,
          ATTRIBUTE10                    ,
          ATTRIBUTE11                    ,
          ATTRIBUTE12                    ,
          ATTRIBUTE13                    ,
          ATTRIBUTE14                    ,
          ATTRIBUTE15                    ,
          ATTRIBUTE_CATEGORY             ,
          NOTE_STATUS                    ,
          ORG_ID                         ,
          MRC_POSTING_CONTROL_ID         ,
          MRC_GL_POSTED_DATE             ,
          MRC_REVERSAL_GL_POSTED_DATE    ,
          MRC_ACCTD_AMOUNT               ,
          MRC_ACCTD_FACTOR_DISC_AMOUNT   ,
          MRC_EXCHANGE_DATE              ,
          MRC_EXCHANGE_RATE              ,
          MRC_EXCHANGE_RATE_TYPE          
       )
       values
       (
          l_cash_receipt_history_id          ,
          CAPARIMP_MAPPING.GetCashReceiptIDMapping(cur.CASH_RECEIPT_ID),
          cur.STATUS                         ,
          cur.TRX_DATE                       ,
          cur.AMOUNT                         ,
          cur.FIRST_POSTED_RECORD_FLAG       ,
          cur.POSTABLE_FLAG                  ,
          cur.FACTOR_FLAG                    ,
          cur.GL_DATE                        ,
          cur.CURRENT_RECORD_FLAG            ,
          CAPARIMP_MAPPING.GetReceiptBatchIDMapping(cur.BATCH_ID),
          CAPBRIMP_MAPPING.GetCCIDMapping(cur.ACCOUNT_CODE_COMBINATION_ID),
          cur.REVERSAL_GL_DATE               ,
          CAPARIMP_MAPPING.GetReversalReceiptHistID(cur.REVERSAL_CASH_RECEIPT_HIST_ID)  ,
          cur.FACTOR_DISCOUNT_AMOUNT         ,
          CAPBRIMP_MAPPING.GetCCIDMapping(cur.BANK_CHARGE_ACCOUNT_CCID),
          cur.POSTING_CONTROL_ID             ,
          cur.REVERSAL_POSTING_CONTROL_ID    ,
          cur.GL_POSTED_DATE                 ,
          cur.REVERSAL_GL_POSTED_DATE        ,
          cur.LAST_UPDATE_LOGIN              ,
          cur.ACCTD_AMOUNT                   ,
          cur.ACCTD_FACTOR_DISCOUNT_AMOUNT   ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
          cur.CREATION_DATE                  ,
          cur.EXCHANGE_DATE                  ,
          cur.EXCHANGE_RATE                  ,
          cur.EXCHANGE_RATE_TYPE             ,
          cur.LAST_UPDATE_DATE               ,
          cur.PROGRAM_APPLICATION_ID         ,
          cur.PROGRAM_ID                     ,
          cur.PROGRAM_UPDATE_DATE            ,
          cur.REQUEST_ID                     ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
          cur.PRV_STAT_CASH_RECEIPT_HIST_ID  ,
          cur.CREATED_FROM                   ,
          cur.REVERSAL_CREATED_FROM          , 
          cur.ATTRIBUTE1                     ,
          cur.ATTRIBUTE2                     ,
          cur.ATTRIBUTE3                     ,
          cur.ATTRIBUTE4                     ,
          cur.ATTRIBUTE5                     ,
          cur.ATTRIBUTE6                     ,
          cur.ATTRIBUTE7                     ,
          cur.ATTRIBUTE8                     ,
          cur.ATTRIBUTE9                     ,
          cur.ATTRIBUTE10                    ,
          cur.ATTRIBUTE11                    ,
          cur.ATTRIBUTE12                    ,
          cur.ATTRIBUTE13                    ,
          cur.ATTRIBUTE14                    ,
          cur.ATTRIBUTE15                    ,
          cur.ATTRIBUTE_CATEGORY             ,
          cur.NOTE_STATUS                    ,
          CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
          cur.MRC_POSTING_CONTROL_ID         ,
          cur.MRC_GL_POSTED_DATE             ,
          cur.MRC_REVERSAL_GL_POSTED_DATE    ,
          cur.MRC_ACCTD_AMOUNT               ,
          cur.MRC_ACCTD_FACTOR_DISC_AMOUNT   ,
          cur.MRC_EXCHANGE_DATE              ,
          cur.MRC_EXCHANGE_RATE              ,
          cur.MRC_EXCHANGE_RATE_TYPE                                 
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_RECEIPT_HISTORY', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
